C:/Projekte/RAIS/FillCustomQueryTableCL/PreselectionListQuery.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace FillCustomQueryTableCL
{
    public class PreselectionListQuery : Query
    {
        private List<string> correctParameters = new List<string>();
        private static string QueryTypeName = "Preselection List";
        private readonly string GetParametersQuery =
            "SELECT " +
            "    0 FromMask, " +
            "    [Dynamic Mask Filter].[Dynamic Mask Filter Name] [Name], " +
            "    [Dynamic Mask Filter].[Order], " +
            "    min([Dynamic Mask Filter].[Query Name]) RelatedQuery, " +
            "    min([Dynamic Mask Filter].[FK Table ID]) RelatedTable, " +
            "    max([Dynamic Mask Filter].[Visible On Form]) [Visible On Form] " +
            "FROM [Dynamic Mask Filter] " +
            "    LEFT JOIN [Dynamic Mask] on [PK Dynamic Mask ID]=[FK Dynamic Mask ID] " +
            "    LEFT JOIN [Dynamic Mask Type] on [PK Dynamic Mask Type ID]=[FK Dynamic Mask Type ID] " +
            "WHERE [Dynamic Mask].[Query Name]='{0}' and [Use In Query]=1 " +
            "GROUP BY [Dynamic Mask Filter].[Dynamic Mask Filter Name], [Dynamic Mask Filter].[Order] " +
            "UNION " +
            "SELECT " +
            "    1 FromSys, sys.parameters.name [Name], sys.parameters.parameter_id, " +
            "    sys.types.name TypeName, null, null " +
            "FROM sys.parameters  " +
            "    LEFT JOIN sys.objects on sys.parameters.object_id=sys.objects.object_id  " +
            "    LEFT JOIN sys.types ON sys.parameters.user_type_id=sys.types.user_type_id  " +
            "WHERE sys.objects.name='{0}' " +
            "ORDER BY FromMask, [Dynamic Mask Filter].[Order] ";

        public PreselectionListQuery(string name)
            : base(name, QueryTypeName)
        {
            LoadParameters(GetParametersQuery);
        }

        public PreselectionListQuery(string name, string queryTypeName, string getParametersQuery)
            : base(name, queryTypeName)
        {
            LoadParameters(getParametersQuery);
        }

        private void LoadParameters(string getParametersQuery)
        {

            using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
            {
                sqlCommand.CommandText = string.Format(getParametersQuery, Name);
                int order = 0;
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    Dictionary<int, string[]> fromMask = new Dictionary<int, string[]>();
                    Dictionary<int, string[]> fromSys = new Dictionary<int, string[]>();
                    
                    while (reader.Read())
                    {
                        if ((int)reader["FromMask"] == 0)
                        {
                            PreСorrectParameters(reader);
                            string tabId;
                            string relQuery;
                            order++;
                            if (reader["RelatedTable"] != DBNull.Value) tabId = reader["RelatedTable"].ToString();
                            else tabId = "-1";
                            if (reader["RelatedQuery"] != DBNull.Value ) relQuery = reader["RelatedQuery"].ToString();
                            else relQuery = "";

                            fromMask[order] = new string[3] { (string)reader["Name"], relQuery, tabId };
                        }
                        else fromSys[(int)reader["Order"]] = new string[2] { (string)reader["Name"], (string)reader["RelatedQuery"] };
                    }
                    order = 1;
                    string type;
                    if (fromMask.Count == 0)
                    {
                        this.Type = QueryTypes["Other"];
                        for (int i = 1; i <= fromSys.Count; i++ )
                            Parameters.Add(new Parameter(Name, fromSys[i][0].Substring(1), fromSys[i][0].Substring(1), GetTypeFromSQLType(fromSys[i][1]), -1, "", i));
                    }
                    else
                    {
                        for (int i = 1; i <= fromMask.Count; i++)
                        {
                            string relQuery = fromMask[i][1];
                            int tabId = int.Parse(fromMask[i][2]);
                            string querName = fromMask[i][0];

                            if (querName == "DatesRange")
                            {
                                type = "DatesRangeStartDate";
                                querName = "StartDate";
                            }
                            else if (querName == "User")
                                type = "UserID";
                            else if (relQuery != "")
                                type = "LookupFromQuery";
                            else if (tabId != -1)
                                type = "LookupFromTable";
                            else type = GetTypeFromSQLType(fromSys[order][1]);
                            
                            Parameters.Add(new Parameter(Name, fromSys[order][0].Substring(1), querName, type, tabId, relQuery, order));
                            order++;
                            if (type == "DatesRangeStartDate")
                            {
                                querName = "EndDate";
                                type = "DatesRangeEndDate";
                                Parameters.Add(new Parameter(Name, fromSys[order][0].Substring(1), querName, type, tabId, relQuery, order));
                                order++;
                            }
                        }
                    }
                    СorrectParameters();
                }
            }
        }

        protected override void PreСorrectParameters(System.Data.SqlClient.SqlDataReader reader)
        {
            if ((int)reader["FromMask"] == 0 && reader["Visible On Form"].ToString() == "0")
                correctParameters.Add(((string)reader["Name"]).ToLower().Trim());
        }
        protected override void СorrectParameters()
        {
            foreach (Parameter par in Parameters)
            {
                if (par.VisibleName.ToLower().Trim() == "facility" && correctParameters.Contains("facility"))
                    par.TypeName = "PreselectedFacility";
                else if (par.VisibleName.ToLower().Trim() == "department" && correctParameters.Contains("department"))
                    par.TypeName = "PreselectedDepartment";
            }
        }
    }
}